package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import connectionMSqL.MySQLConn;
import entity.*;

public class MateriaDAO {

	public static Materia createMateria(ResultSet rs) throws SQLException {
		
		Materia mat = new Materia();
		mat.setId(rs.getInt("id"));
		mat.setNome(rs.getString("nome"));
		mat.setDescricao(rs.getString("descricao"));

		return mat;
	}
	
	public static boolean insert(Materia m) {
	
		boolean salvou = false;
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			int paramIndex = 1;
			ps = conn
					.prepareStatement("INSERT INTO materia (nome , descricao)"
							+ "VALUES (?,?)");
			ps.setString(paramIndex++, m.getNome());
			ps.setString(paramIndex++, m.getDescricao());
			
			ps.executeUpdate();
			salvou = true;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					ps.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return salvou;
	}

	public static boolean save(Materia mat) {
		boolean salvou = false;

		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			int paramIndex = 1;
			ps = conn
					.prepareStatement("INSERT INTO materia ('nome','descricao)"
							+ "VALUES (?,?)");
			ps.setString(paramIndex++, mat.getNome());
			ps.setString(paramIndex++, mat.getDescricao());
			
			ps.executeUpdate();
			salvou = true;

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					ps.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return salvou;
	}

	public static List<Materia> getAll() {
		List<Materia> materias = new ArrayList<Materia>();
		
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM materia");
			rs = ps.executeQuery();
			while (rs.next()) {
				Materia mat = createMateria(rs);
				materias.add(mat);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return materias;
	}

	public static boolean update(Materia mat) {
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			String query = ("UPDATE materia" + " SET nome='" + mat.getNome() + "'" + ",descricao ='"
					+ mat.getDescricao() + "'"  
					+ "WHERE id =" + mat.getId());
			ps = conn.prepareStatement(query);

			// teste query
			System.out.print(query);
			ps.executeUpdate();
			return true;
			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}
		return false;
	}

	public static List<Materia> findById(int id) {
		
		List<Materia> materia = new ArrayList<Materia>();
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM materia WHERE id="+id);
			rs = ps.executeQuery();
			while (rs.next()) {

				Materia m = createMateria(rs);
				materia.add(m);
			}

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
			}
		}

		return materia;
	}

    public static boolean delete(int id) {
		
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("DELETE FROM materia WHERE id="+ id);
			ps.executeUpdate();

		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return false;
			} finally {
			try {
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();

			} catch (SQLException e) {
				System.out.println(e.getMessage());
				return false;
			}
		}

		return true;
	}

		public static Materia findByIdRetObj(Integer id) {
		Materia materia = null;
		Connection conn = MySQLConn.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn
					.prepareStatement(" SELECT * FROM materia WHERE id= " + id);
			rs = ps.executeQuery();
			if (rs.next()) {
				materia = createMateria(rs);
				
			}

		} catch (SQLException e) {
			System.out.print("Erro MateriaDAO.findByIdRetObj: " + e.getMessage());
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (ps != null)
					ps.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				System.out.print("Erro MateriaDAO.findByIdRetObj: " + e.getMessage());
			}
		}
		return materia;
		}
}//end